/**
 * gislin 2017年7月11日
 */
package com.btcode.db.executer;

import com.btcode.db.config.DataBaseConfig;
import com.btcode.db.connection.IConnectionGetter;
import com.btcode.db.conver.DefaultValueConver;
import com.btcode.db.conver.IValueConver;
import com.btcode.db.result.PageResult;
import com.btcode.db.result.Row;
import com.btcode.db.sqlparameter.PageInfo;
import com.btcode.db.sqlparameter.SQLParameterList;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author gislin 2017年7月11日
 */
public class SQLServerDataExecuter extends BaseDataExecuter {

    public SQLServerDataExecuter(DataBaseConfig config, IConnectionGetter connectionGetter) {
        super(config, connectionGetter);
    }

    @Override
    public PageResult queryListPage(String sql, SQLParameterList parms, PageInfo pageInfo) {

        String orderBy = getOrderByString(sql);

        sql = sql.trim().toUpperCase();
        sql = sql.replaceFirst("SELECT", "SELECT TOP 100 PERCENT");

        String countSQL = " SELECT COUNT(*) FROM (" + sql + ") t ";
        Object countObj = queryScalar(countSQL, parms);

        IValueConver conver = new DefaultValueConver();
        int recordCount = conver.converToInteger(countObj);
        int pageCount = recordCount > 0?(int) Math.ceil(recordCount * 1.0 / pageInfo.getPageSize()):1;
        int pageIndex = pageInfo.getPageIndex() > pageCount ? pageCount : pageInfo.getPageIndex();
        int offset = (pageIndex - 1) * pageInfo.getPageSize();

        pageInfo.setPageCount(pageCount);
        pageInfo.setPageIndex(pageIndex);
        pageInfo.setRecordCount(recordCount);

        String pageSql =  " SELECT * FROM ( "
            + "	SELECT row_number()over(order by tempcolumn) temprownumber,* FROM ( "
            + "	SELECT TOP " + (offset+pageInfo.getPageSize()) +" tempcolumn=0,* FROM ( "
            +sql+") t "+orderBy+" ) t ) t where temprownumber> "+(offset);

        List<Row> rows = executeQuery(pageSql, parms);

        PageResult result = new PageResult(pageInfo, rows);

        return result;
    }

    /**
     * 获取order by语段，用于解决SqlServer分页无法排序的问题
     * @param sql
     * @return
     */
    private static String getOrderByString(String sql){

        int lastIndexOfOB = sql.toLowerCase().lastIndexOf(" order ");
        int lastIndexofBrackets = sql.lastIndexOf(")");

        if(lastIndexOfOB < 0){
            return "";
        }

        if(lastIndexofBrackets > lastIndexOfOB){
            return "";
        }
        else {
            return sql.toLowerCase().substring(lastIndexOfOB,sql.length());
        }
    }

    public static void main(String[] args){
        String sql1 = " SELECT * FROM TABLE WHERE 1=1 ";
        String sql2 = " SELECT * FROM TABLE WHERE 1=1 ORDER BY NAME DESC ";
        String sql3 = "SELECT * FROM ( SELECT * FROM TABLE WHERE 1=1 ORDER BY NAME DESC ) WHERE 1=1";
        System.out.println(getOrderByString(sql1));
        System.out.println(getOrderByString(sql2));
        System.out.println(getOrderByString(sql3));
    }

}
